package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.peenongsaban.freelance.bean.AptitudeBean;
import com.peenongsaban.freelance.bean.AptitudeTypeBean;
import com.peenongsaban.freelance.bean.PaymentOfServiceBean;
import com.peenongsaban.freelance.bean.PaymentTypeBean;
import com.peenongsaban.freelance.bean.PictureBean;
import com.peenongsaban.freelance.bean.PostJobBean;
import com.peenongsaban.freelance.bean.PostServiceBean;
import com.peenongsaban.freelance.bean.ServiceBean;
import com.peenongsaban.freelance.bean.UserBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.HomePageDAO;
import com.peenongsaban.freelance.meta.AptitudeOfJobViewMeta;
import com.peenongsaban.freelance.meta.AptitudeTypeMeta;
import com.peenongsaban.freelance.meta.ListPostJobViewMeta;
import com.peenongsaban.freelance.meta.PaymentOfServiceMeta;
import com.peenongsaban.freelance.meta.PaymentTypeMeta;
import com.peenongsaban.freelance.meta.PictureMeta;
import com.peenongsaban.freelance.meta.PostJobMeta;
import com.peenongsaban.freelance.meta.PostJobPromotionMeta;
import com.peenongsaban.freelance.meta.PostServiceMeta;
import com.peenongsaban.freelance.meta.ServiceMeta;
import com.peenongsaban.freelance.meta.UserMeta;
import com.peenongsaban.freelance.meta.ViewMeta;
import com.peenongsaban.freelance.utils.ConstantUtils;
import com.peenongsaban.freelance.utils.ExceptionUtil;

public class HomePageDAOImpl implements HomePageDAO{

	@Override
	public Vector<AptitudeTypeBean> listAptitudeType() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<AptitudeTypeBean> vector = new Vector<AptitudeTypeBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM " + AptitudeTypeMeta.TABLE;
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				AptitudeTypeBean bean = new AptitudeTypeBean();
				bean.setAptitudeTypeID(rs
						.getInt(AptitudeTypeMeta.COL_APTITUDE_TYPE_ID));
				bean.setAptitudeTypeName(rs
						.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));
				bean.setImage_icon(rs
						.getString(AptitudeTypeMeta.COL_IMAGE_ICON));
				bean.setDescription(rs
						.getString(AptitudeTypeMeta.COL_DESCRIPTION));
				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				if (statement != null)
					statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<PostJobBean> listJobMostPopular() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostJobBean> vector = new Vector<PostJobBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM " + ViewMeta.LIST_POST_JOB_V
					+ " ORDER BY " + PostJobMeta.COL_AMOUNT_USER_REQUEST_JOB
					+ " DESC LIMIT 0,21";

			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PostJobBean bean = new PostJobBean();
				bean.setPostID(rs.getString(ListPostJobViewMeta.COL_POST_ID));
				bean.getUser().setIdCard(
						rs.getString(ListPostJobViewMeta.COL_ID_CARD));
				bean.getUser().setTrustLevel(
						rs.getDouble(ListPostJobViewMeta.COL_TRUST_LEVEL));
				bean.setPostDate(rs.getDate(ListPostJobViewMeta.COL_POST_DATE));
				bean.getJob().setJobName(
						rs.getString(ListPostJobViewMeta.COL_JOB_NAME));
				bean.getJob().setDescriptionOfJob(rs
						.getString(ListPostJobViewMeta.COL_DESCRIPTION_OF_JOB));
				bean.getJob().setFormatTime(
						rs.getString(ListPostJobViewMeta.COL_FORMAT_TIME));
				bean.getJob().setWorkArea(
						rs.getString(ListPostJobViewMeta.COL_WORK_AREA));
				bean.getJob().setStartDateOfJob(
						rs.getDate(ListPostJobViewMeta.COL_START_DATE_OF_JOB));
				bean.getJob().setEndDateOfJob(
						rs.getDate(ListPostJobViewMeta.COL_END_DATE_OF_JOB));
				bean.getJob()
						.getPaymentOfJob()
						.setFixPrice(
								rs.getDouble(ListPostJobViewMeta.COL_FIX_PRICE));
				bean.getJob()
						.getPaymentOfJob()
						.setMinPrice(
								rs.getDouble(ListPostJobViewMeta.COL_MIN_PRICE));
				bean.getJob()
						.getPaymentOfJob()
						.setMaxPrice(
								rs.getDouble(ListPostJobViewMeta.COL_MAX_PRICE));
				bean.setAmountRequestPost(rs
						.getInt(ListPostJobViewMeta.COL_AMOUNT_USER_REQUEST_JOB));
				vector.add(bean);
			}

			for (PostJobBean bean : vector) {
				Vector<AptitudeBean> apVector = new Vector<AptitudeBean>();
				statement = null;
				sql = "SELECT * FROM " + AptitudeOfJobViewMeta.VIEW + " WHERE "
						+ AptitudeOfJobViewMeta.COL_POST_ID + " = ?";
				statement = conn.prepareStatement(sql);
				statement.setString(1, bean.getPostID());
				rs = statement.executeQuery();
				while (rs.next()) {
					AptitudeBean ap = new AptitudeBean();
					ap.setAptitudeID(Integer.parseInt(rs
							.getString(AptitudeOfJobViewMeta.COL_APTITUDE_ID)));
					ap.setAptitudeName(rs
							.getString(AptitudeOfJobViewMeta.COL_APTITUDE_NAME));
					ap.setDescription(rs
							.getString(AptitudeOfJobViewMeta.COL_DESCRIPTION));
					ap.getAptitudeType().setAptitudeTypeID(
							rs.getInt(AptitudeOfJobViewMeta.COL_APTITUDE_ID));
					ap.getAptitudeType()
							.setAptitudeTypeName(
									rs.getString(AptitudeOfJobViewMeta.COL_APTITUDE_TYPE_NAME));
					apVector.add(ap);
				}
				bean.setListAptitude(apVector);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			databaseConnection.destroy();
			databaseConnection = null;
			try {
				statement.close();
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<PostJobBean> listJobUrgent() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostJobBean> vector = new Vector<PostJobBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM " + PostJobPromotionMeta.TABLE
					+ " PRO LEFT JOIN " + ListPostJobViewMeta.VIEW_CONDITON
					+ " LI ON PRO." + ListPostJobViewMeta.COL_POST_ID + "=LI."
					+ PostJobPromotionMeta.COL_POST_ID + " WHERE PRO."
					+ PostJobPromotionMeta.COL_PROMOTION_ID + "="+ConstantUtils.POST_JOB_URGENT_CODE;

			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PostJobBean bean = new PostJobBean();
				bean.setPostID(rs.getString(ListPostJobViewMeta.COL_POST_ID));
				bean.getUser().setIdCard(
						rs.getString(ListPostJobViewMeta.COL_ID_CARD));
				bean.getUser().setTrustLevel(
						rs.getDouble(ListPostJobViewMeta.COL_TRUST_LEVEL));
				bean.setPostDate(rs.getDate(ListPostJobViewMeta.COL_POST_DATE));
				bean.getJob().setJobName(
						rs.getString(ListPostJobViewMeta.COL_JOB_NAME));
				bean.getJob().setDescriptionOfJob(rs
						.getString(ListPostJobViewMeta.COL_DESCRIPTION_OF_JOB));
				bean.getJob().setFormatTime(
						rs.getString(ListPostJobViewMeta.COL_FORMAT_TIME));
				bean.getJob().setWorkArea(
						rs.getString(ListPostJobViewMeta.COL_WORK_AREA));
				bean.getJob().setStartDateOfJob(
						rs.getDate(ListPostJobViewMeta.COL_START_DATE_OF_JOB));
				bean.getJob().setEndDateOfJob(
						rs.getDate(ListPostJobViewMeta.COL_END_DATE_OF_JOB));
				bean.getJob()
						.getPaymentOfJob()
						.setFixPrice(
								rs.getDouble(ListPostJobViewMeta.COL_FIX_PRICE));
				bean.getJob()
						.getPaymentOfJob()
						.setMinPrice(
								rs.getDouble(ListPostJobViewMeta.COL_MIN_PRICE));
				bean.getJob()
						.getPaymentOfJob()
						.setMaxPrice(
								rs.getDouble(ListPostJobViewMeta.COL_MAX_PRICE));
				bean.setAmountRequestPost(rs
						.getInt(ListPostJobViewMeta.COL_AMOUNT_USER_REQUEST_JOB));
				vector.add(bean);
			}

			for (PostJobBean bean : vector) {
				Vector<AptitudeBean> apVector = new Vector<AptitudeBean>();
				statement = null;
				sql = "SELECT * FROM " + AptitudeOfJobViewMeta.VIEW + " WHERE "
						+ AptitudeOfJobViewMeta.COL_POST_ID + " = ?";
				statement = conn.prepareStatement(sql);
				statement.setString(1, bean.getPostID());
				rs = statement.executeQuery();
				while (rs.next()) {
					AptitudeBean ap = new AptitudeBean();
					ap.setAptitudeID(Integer.parseInt(rs
							.getString(AptitudeOfJobViewMeta.COL_APTITUDE_ID)));
					ap.setAptitudeName(rs
							.getString(AptitudeOfJobViewMeta.COL_APTITUDE_NAME));
					ap.setDescription(rs
							.getString(AptitudeOfJobViewMeta.COL_DESCRIPTION));
					ap.getAptitudeType().setAptitudeTypeID(
							rs.getInt(AptitudeOfJobViewMeta.COL_APTITUDE_ID));
					ap.getAptitudeType()
							.setAptitudeTypeName(
									rs.getString(AptitudeOfJobViewMeta.COL_APTITUDE_TYPE_NAME));
					apVector.add(ap);
				}
				bean.setListAptitude(apVector);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			databaseConnection.destroy();
			databaseConnection = null;
			try {
				statement.close();
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<PostServiceBean> listServiceMostPopular() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostServiceBean> postServiceList = new Vector<PostServiceBean>();
		PreparedStatement select_service_statement = null;
		PreparedStatement select_payment_statement = null;
		PreparedStatement select_picture_statement = null;
		try {
			String select_service_sql = "SELECT * FROM "+ViewMeta.LIST_POST_SERVICE_V+" ORDER BY "+PostServiceMeta.COL_AMOUNT_REQUEST_POST+" DESC";
			select_service_statement = conn.prepareStatement(select_service_sql);
			ResultSet rs_service = select_service_statement.executeQuery();
			while (rs_service.next()) {
				PostServiceBean postService = new PostServiceBean();
				postService.setPostID(rs_service.getString(PostServiceMeta.COL_POST_ID));
				postService.setPostDate(rs_service.getDate(PostServiceMeta.COL_POST_DATE));
				postService.setAmountRequestPost(rs_service.getInt(PostServiceMeta.COL_AMOUNT_REQUEST_POST)); 
				
				UserBean user = new UserBean();
				user.setPicture(rs_service.getString(UserMeta.COL_PICTURE));
				user.setIdCard(rs_service.getString(UserMeta.COL_ID_CARD));
				user.setAlias(rs_service.getString(UserMeta.COL_ALIAS));
				user.setName_th(rs_service.getString(UserMeta.COL_NAME_TH));
				user.setLastName_th(rs_service.getString(UserMeta.COL_LASTNAME_TH));
				user.setTrustLevel(rs_service.getDouble(UserMeta.COL_TRUST_LEVEL));
				
				postService.setUser(user);				
				
				ServiceBean service = new ServiceBean();
				service.setServiceID(rs_service.getString(ServiceMeta.COL_SERVICE_ID));
				service.setServiceName(rs_service.getString(ServiceMeta.COL_SERVICE_NAME));
				service.setDescriptionOfService(rs_service.getString(ServiceMeta.COL_DESCRIPTION_OF_SERVICE));
				
				postService.setService(service);
								
				Vector<PaymentOfServiceBean> listPaymentOfService = new Vector<PaymentOfServiceBean>();
				String select_payment_sql = "SELECT * FROM "+ViewMeta.PAYMENT_OF_SERVICE_V+" WHERE "+ServiceMeta.COL_SERVICE_ID+" = ?";
				select_payment_statement = conn.prepareStatement(select_payment_sql);
				select_payment_statement.setString(1, postService.getService().getServiceID());
				ResultSet rs_payment = select_payment_statement.executeQuery();
				while (rs_payment.next()) {
					PaymentOfServiceBean payment = new PaymentOfServiceBean();
					payment.setPaymentId(rs_payment.getString(PaymentOfServiceMeta.COL_PAYMENT_ID));
					payment.setPrice(rs_payment.getDouble(PaymentOfServiceMeta.COL_PRICE));
					payment.setDescription(rs_payment.getString(PaymentOfServiceMeta.COL_DESCRIPTION));
					
					PaymentTypeBean paymentType = new PaymentTypeBean();
					paymentType.setPaymentTypeName(rs_payment.getString(PaymentTypeMeta.COL_PAYMENT_TYPE_NAME)); 
					paymentType.setPaymentTypeID(rs_payment.getInt(PaymentTypeMeta.COL_PAYMENT_TYPE_ID));
					
					payment.setPaymentType(paymentType);
				
					Vector<PictureBean> listPicture = new Vector<PictureBean>();
					String select_picture_sql = "SELECT * FROM "+ViewMeta.PICTURE_OF_SERVICE_V+" WHERE "+PaymentOfServiceMeta.COL_PAYMENT_ID+" = ?";
					select_picture_statement = conn.prepareStatement(select_picture_sql);
					select_picture_statement.setString(1, payment.getPaymentId());
					ResultSet rs_picture = select_picture_statement.executeQuery();
					while (rs_picture.next()) {
						PictureBean picture = new PictureBean();
						picture.setPictureID(rs_picture.getString(PictureMeta.COL_PICTURE_ID));
						picture.setPictureLocation(rs_picture.getString(PictureMeta.COL_PICTURE_LOCATION));
						picture.setDescription(rs_picture.getString(PictureMeta.COL_DESCRIPTION));
						listPicture.add(picture);
					}
					
					payment.setListPicture(listPicture);
					
					listPaymentOfService.add(payment);
				}
				
				service.setListPaymentOfService(listPaymentOfService); 
				
				postService.setService(service);
				
				postServiceList.add(postService);
			}
			
			databaseConnection.freeConnection(conn);
			
			return postServiceList;
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				if(select_service_statement != null)
					select_service_statement.close();
				if(select_payment_statement != null)
					select_payment_statement.close();
				if(select_picture_statement != null)
					select_picture_statement.close();
				
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return null;
	}	
}
